--    Program: 		CAPARIMP_CUSTOMER_PARTY_SITE.sql
--    Description:  Import AR CUSTOMER PARTY SITE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_party_site_id			number;
   l_party_site_number		varchar2(30);
                    		                 		
   cursor cur_ar_customer_party_site is
      select * from CRST_HZ_PARTY_SITES
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_party_site loop   	
     l_err_msg_tmp := '';
     
     select HZ_PARTY_SITES_S.NEXTVAL 
     INTO l_party_site_id
     FROM DUAL;
     
     update HZ_PARTY_SITES
     set    party_site_number = substr(party_site_number||'X', 1, 30)
     where  party_site_id = cur.party_site_id;     
     
     update CRST_HZ_PARTY_SITES
     set    party_site_id_n = l_party_site_id,
            party_site_number_n = cur.party_site_number
     where  party_site_id = cur.party_site_id; 
     
     begin
       -- import data
       insert into HZ_PARTY_SITES
       (
              PARTY_SITE_ID              ,                
              PARTY_ID                   ,                
              LOCATION_ID                ,                
              LAST_UPDATE_DATE           ,                
              PARTY_SITE_NUMBER          ,                
              LAST_UPDATED_BY            ,                
              CREATION_DATE              ,                
              CREATED_BY                 ,                
              LAST_UPDATE_LOGIN          ,                
              REQUEST_ID                 ,                
              PROGRAM_APPLICATION_ID     ,                
              PROGRAM_ID                 ,                
              PROGRAM_UPDATE_DATE        ,                
              WH_UPDATE_DATE             ,                
              ATTRIBUTE_CATEGORY         ,                
              ATTRIBUTE1                 ,                
              ATTRIBUTE2                 ,                
              ATTRIBUTE3                 ,                
              ATTRIBUTE4                 ,                
              ATTRIBUTE5                 ,                
              ATTRIBUTE6                 ,                
              ATTRIBUTE7                 ,                
              ATTRIBUTE8                 ,                
              ATTRIBUTE9                 ,                
              ATTRIBUTE10                ,                
              ATTRIBUTE11                ,                
              ATTRIBUTE12                ,                
              ATTRIBUTE13                ,                
              ATTRIBUTE14                ,                
              ATTRIBUTE15                ,                
              ATTRIBUTE16                ,                
              ATTRIBUTE17                ,                
              ATTRIBUTE18                ,                
              ATTRIBUTE19                ,                
              ATTRIBUTE20                ,                
              GLOBAL_ATTRIBUTE_CATEGORY  ,                
              GLOBAL_ATTRIBUTE1          ,                
              GLOBAL_ATTRIBUTE2          ,                
              GLOBAL_ATTRIBUTE3          ,                
              GLOBAL_ATTRIBUTE4          ,                
              GLOBAL_ATTRIBUTE5          ,                
              GLOBAL_ATTRIBUTE6          ,                
              GLOBAL_ATTRIBUTE7          ,                
              GLOBAL_ATTRIBUTE8          ,                
              GLOBAL_ATTRIBUTE9          ,                
              GLOBAL_ATTRIBUTE10         ,                
              GLOBAL_ATTRIBUTE11         ,                
              GLOBAL_ATTRIBUTE12         ,                
              GLOBAL_ATTRIBUTE13         ,                
              GLOBAL_ATTRIBUTE14         ,                
              GLOBAL_ATTRIBUTE15         ,                
              GLOBAL_ATTRIBUTE16         ,                
              GLOBAL_ATTRIBUTE17         ,                
              GLOBAL_ATTRIBUTE18         ,                
              GLOBAL_ATTRIBUTE19         ,                
              GLOBAL_ATTRIBUTE20         ,                
              ORIG_SYSTEM_REFERENCE      ,                
              START_DATE_ACTIVE          ,                
              END_DATE_ACTIVE            ,                
              REGION                     ,                
              MAILSTOP                   ,                
              CUSTOMER_KEY_OSM           ,                
              PHONE_KEY_OSM              ,                
              CONTACT_KEY_OSM            ,                
              IDENTIFYING_ADDRESS_FLAG   ,                
              LANGUAGE                   ,                
              STATUS                     ,                
              PARTY_SITE_NAME            ,                
              ADDRESSEE                  ,                
              OBJECT_VERSION_NUMBER      ,                
              CREATED_BY_MODULE          ,                
              APPLICATION_ID             ,                
              ACTUAL_CONTENT_SOURCE                       
       )
       values
       (
            l_party_site_id                ,
            CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.PARTY_ID),
            CAPARIMP_MAPPING.GetHZLocationIDMapping(cur.LOCATION_ID),
            cur.LAST_UPDATE_DATE           ,
            cur.PARTY_SITE_NUMBER          ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE              ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN          ,
            cur.REQUEST_ID                 ,
            cur.PROGRAM_APPLICATION_ID     ,
            cur.PROGRAM_ID                 ,
            cur.PROGRAM_UPDATE_DATE        ,
            cur.WH_UPDATE_DATE             ,
            cur.ATTRIBUTE_CATEGORY         ,
            cur.ATTRIBUTE1                 ,
            cur.ATTRIBUTE2                 ,
            cur.ATTRIBUTE3                 ,
            cur.ATTRIBUTE4                 ,
            cur.ATTRIBUTE5                 ,
            cur.ATTRIBUTE6                 ,
            cur.ATTRIBUTE7                 ,
            cur.ATTRIBUTE8                 ,
            cur.ATTRIBUTE9                 ,
            cur.ATTRIBUTE10                ,
            cur.ATTRIBUTE11                ,
            cur.ATTRIBUTE12                ,
            cur.ATTRIBUTE13                ,
            cur.ATTRIBUTE14                ,
            cur.ATTRIBUTE15                ,
            cur.ATTRIBUTE16                ,
            cur.ATTRIBUTE17                ,
            cur.ATTRIBUTE18                ,
            cur.ATTRIBUTE19                ,
            cur.ATTRIBUTE20                ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY  ,
            cur.GLOBAL_ATTRIBUTE1          ,
            cur.GLOBAL_ATTRIBUTE2          ,
            cur.GLOBAL_ATTRIBUTE3          ,
            cur.GLOBAL_ATTRIBUTE4          ,
            cur.GLOBAL_ATTRIBUTE5          ,
            cur.GLOBAL_ATTRIBUTE6          ,
            cur.GLOBAL_ATTRIBUTE7          ,
            cur.GLOBAL_ATTRIBUTE8          ,
            cur.GLOBAL_ATTRIBUTE9          ,
            cur.GLOBAL_ATTRIBUTE10         ,
            cur.GLOBAL_ATTRIBUTE11         ,
            cur.GLOBAL_ATTRIBUTE12         ,
            cur.GLOBAL_ATTRIBUTE13         ,
            cur.GLOBAL_ATTRIBUTE14         ,
            cur.GLOBAL_ATTRIBUTE15         ,
            cur.GLOBAL_ATTRIBUTE16         ,
            cur.GLOBAL_ATTRIBUTE17         ,
            cur.GLOBAL_ATTRIBUTE18         ,
            cur.GLOBAL_ATTRIBUTE19         ,
            cur.GLOBAL_ATTRIBUTE20         ,
            cur.ORIG_SYSTEM_REFERENCE      ,
            cur.START_DATE_ACTIVE          ,
            cur.END_DATE_ACTIVE            ,
            cur.REGION                     ,
            cur.MAILSTOP                   ,
            cur.CUSTOMER_KEY_OSM           ,
            cur.PHONE_KEY_OSM              ,
            cur.CONTACT_KEY_OSM            ,
            cur.IDENTIFYING_ADDRESS_FLAG   ,
            cur.LANGUAGE                   ,
            cur.STATUS                     ,
            cur.PARTY_SITE_NAME            ,
            cur.ADDRESSEE                  ,
            cur.OBJECT_VERSION_NUMBER      ,
            cur.CREATED_BY_MODULE          ,
            cur.APPLICATION_ID             ,
            cur.ACTUAL_CONTENT_SOURCE             
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_PARTY_SITE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
